//
//  LocalInterface.m
//  iHealthEHR
//
//  Created by Joseph Ayo Akinyele on 6/2/10.
//  Copyright 2010 Johns Hopkins University. All rights reserved.
//

#import "SqliteInterface.h"

#define TABLE	"records"
#define ID_COL	"id"
#define SEC_COL "category"
#define ENC_COL "encrypted"
#define CAC_COL "cachetime"

@implementation SqliteInterface

-(id) initWith:(NSString *) username 
{
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);	
	NSString *documentsDirectory = [paths objectAtIndex:0];	
	NSString *dbname = [NSString stringWithFormat:@"%s.sqlite", [username UTF8String]];
	NSLog(@"initWith: '%@'", dbname);
	NSString *path = [documentsDirectory stringByAppendingPathComponent:dbname];		
	// Open the database. The database was prepared outside the application.	
	recordTableExists = NO;
	if (sqlite3_open([path UTF8String], &dbConnection) == SQLITE_OK) {
		NSLog(@"Database Successfully Opened");
		state = CONNECTED;
		// check if table exists? if so, set recordTable exists.
		const char *create_stmt = "SELECT name FROM sqlite_master WHERE name='"TABLE"'";
		sqlite3_stmt *comp_stmt;
		int error;
		if(sqlite3_prepare_v2(dbConnection, create_stmt, -1, &comp_stmt, NULL) == SQLITE_OK) {			
			if((error = sqlite3_step(comp_stmt)) == SQLITE_ROW) {
				NSLog(@""TABLE" exists already");
				recordTableExists = YES;
			}
		}
		sqlite3_finalize(comp_stmt);
				
	} else {		
		NSLog(@"Error in opening database :(");
		state = NOT_CONNECTED;
	}	
	
	return self;
}

// Note: no consequence if table already exists. fail silently.
-(void) createRecordTable
{
	if(state == CONNECTED && recordTableExists == NO) {
		const char *create_stmt = "CREATE TABLE "TABLE" ("ID_COL" varchar(15) unique primary key, "SEC_COL" varchar(15), "ENC_COL" blob, "CAC_COL" date);";
		const char *trigger = "CREATE TRIGGER insert_"TABLE"_"CAC_COL" AFTER INSERT ON records "
		"BEGIN UPDATE "TABLE" SET "CAC_COL" = DATETIME('NOW') WHERE rowid = new.rowid; END;";
		sqlite3_stmt *comp_stmt;
		int error;
		if(sqlite3_prepare_v2(dbConnection, create_stmt, -1, &comp_stmt, NULL) == SQLITE_OK) {			
			if((error = sqlite3_step(comp_stmt)) == SQLITE_DONE) {				
				if(sqlite3_prepare_v2(dbConnection, trigger, -1, &comp_stmt, NULL) == SQLITE_OK) {
					error = sqlite3_step(comp_stmt);
					printf("Error code: '%i'\n", error);
				}
				/* records table has been created successfully */
				recordTableExists = YES;
			}
			else {
				LOG_CONSOLE("Could not create "TABLE" in database.");
			}

		}
		// release compiled statement
		sqlite3_finalize(comp_stmt);
	}
	
}

/* Check if a given record id is present in the database. */
-(BOOL) checkID:(NSString *) ID
{
	BOOL _found = NO;
	sqlite3_stmt *comp_stmt;
	
	if(state == CONNECTED && recordTableExists) {
		NSString *stmt = [[NSString alloc] initWithFormat:@"SELECT "ID_COL" FROM "TABLE" WHERE "ID_COL"='%s';", [ID UTF8String]];
		if(sqlite3_prepare_v2(dbConnection, [stmt UTF8String], -1, &comp_stmt, NULL) == SQLITE_OK) {
			if(sqlite3_step(comp_stmt) == SQLITE_ROW) {
				_found = YES;
			}
		}
		sqlite3_finalize(comp_stmt);
		[stmt release];
	}
	else {
		LOG_CONSOLE(TABLE"does not exist.");
	}
	return _found;
}

// Note: inserts an encrypted medical record. We accept an encrypted medical record.
-(int) insertCCR:(NSString *) ID inSection:(NSString *) section withXML:(NSXMLElement *) xmlEnc
{
	int error = SQLITE_ERROR;
	sqlite3_stmt *comp_stmt;
	NSString *stmt;
	
	if (state == CONNECTED && recordTableExists) {
		// take apart the record: retrieve id, category, and 		
		if([self checkID:ID]) { // ID does exist, proceed to execute update query
			stmt = [[NSString alloc] initWithFormat:@"UPDATE "TABLE" SET "ENC_COL"='%s',"CAC_COL"=DATETIME('NOW') WHERE "ID_COL"='%s';", 
						[[xmlEnc XMLString] UTF8String], [ID UTF8String]];			
		}
		else { // ID does not exist in records table, proceed to execute insert query
			stmt = [[NSString alloc] initWithFormat:@"INSERT INTO "TABLE" VALUES('%s', '%s', '%s', NULL);", 
						  [ID UTF8String], [section UTF8String], [[xmlEnc XMLString] UTF8String]];
		}
		
		if(sqlite3_prepare_v2(dbConnection, [stmt UTF8String], -1, &comp_stmt, NULL) == SQLITE_OK) {
			error = sqlite3_step(comp_stmt);
				// printf("insertCCR result => '%i'\n", error); 
		}
		else {
			LOG_CONSOLE("Could not insert or update CCR entry with id = '%s'", [ID UTF8String]);
		}

		[stmt release];
		// release compiled statement
		sqlite3_finalize(comp_stmt);

	}
	return error;
}

-(NSArray *) retrieveCCR:(NSString *) section
{
	sqlite3_stmt *comp_stmt;
	NSMutableArray *ccrs = [[NSMutableArray alloc] init];
	NSError *error = nil;
	
	if(state == CONNECTED && recordTableExists) {
		NSString *stmt = [[NSString alloc] initWithFormat:@"SELECT "ENC_COL" FROM "TABLE" WHERE "SEC_COL"='%s'", [section UTF8String]];
		if(sqlite3_prepare_v2(dbConnection, [stmt UTF8String], -1, &comp_stmt, NULL) == SQLITE_OK) {
			while(sqlite3_step(comp_stmt) == SQLITE_ROW) {
				CCRObject *c = [[CCRObject alloc] init];
				NSString *encXMLString = [[NSString alloc] initWithFormat:@"%s", (char *)sqlite3_column_text(comp_stmt, 0)];				
				NSXMLElement *encXML = [[NSXMLElement alloc] initWithXMLString:encXMLString error:&error];
				
				/* Safe to assume already encrypted */
				c.item = ENCRYPTED_ENTRY;
				c.ccrsection = section;
				c.XmlElement = encXML;
				// set c.editDate here to what was cached? 
				[c initOtherXMLObjects];
				
				[ccrs addObject:c];
				[encXMLString release];
				[encXML release];
				[c release];
			} 
		}
		// release compiled statement
		sqlite3_finalize(comp_stmt);
	}
	
	if ([ccrs count] == 0) {
		NSLog(@"No records retrieved for: %@", section);
		[ccrs release];
		return nil;
	}
	
	/* caller must free object */
	return ccrs;
}

-(void) dealloc
{
	sqlite3_close(dbConnection);
	NSLog(@"Releasing objects.");
	[super dealloc];
}

@end

/* 
 // TEST CODE 
 SqliteInterface *sql = [[SqliteInterface alloc] initWith:username.text];
 [sql createRecordTable];
 NSError *error = nil;
 NSXMLElement *test = [[NSXMLElement alloc] initWithXMLString:@"<Body><Encrypted id='asdbse2'><ABE>blob2</ABE><EncryptedData>Ciphertext 2</EncryptedData></Encrypted></Body>" error:&error];
 NSXMLElement *test1 = [[NSXMLElement alloc] initWithXMLString:@"<Body><Encrypted id='asdae3'><ABE>blob3</ABE><EncryptedData>Ciphertext 3</EncryptedData></Encrypted></Body>" error:&error];
 
 [sql createRecordTable];
 // use to cache each record. Errors: duplicate entry or db missing or not created
 int err = [sql insertCCR:@"asdbse2" inSection:@"MEDICATION" withXML:test];
 err = [sql insertCCR:@"asdade3" inSection:@"MEDICATION" withXML:test1];
 
 NSArray *ccrObjects = [sql retrieveCCR:@"MEDICATION"];
 for (int i = 0; i < [ccrObjects count]; i++) {
	CCRObject *c = [ccrObjects objectAtIndex:i];
	[c printObject];
 }
 
 [ccrObjects release];
 [test release];
 [test1 release];
 [sql release];
*/ 

